package com.hs.device.charger.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.hs.communication.common.entity.DateRange;
import com.hs.communication.common.entity.vo.DayChargingEnergy;
import com.hs.communication.common.entity.vo.DayChargingTime;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.springframework.data.repository.query.Param;

import java.util.List;

@Mapper
public interface DateRangeMapper extends BaseMapper<DateRange> {

    @Results({
            @Result(property = "date", column = "charging_date"),
            @Result(property = "energy", column = "total_charging_energy")
    })
    @Select("WITH RECURSIVE DateRange AS (" +
            "    SELECT CURDATE() - INTERVAL 6 DAY AS reporting_date " +
            "    UNION ALL " +
            "    SELECT reporting_date + INTERVAL 1 DAY " +
            "    FROM DateRange " +
            "    WHERE reporting_date < CURDATE() " +
            ") " +
            "SELECT DATE(dr.reporting_date) AS charging_date, COALESCE(SUM(dc.charging_energy), 0) AS total_charging_energy " +
            "FROM DateRange dr " +
            "LEFT JOIN hs_device_charger_data dc ON DATE(dc.report_time) = DATE(dr.reporting_date) " +
            "LEFT JOIN hs_device_user_charger uc ON dc.sn = uc.charger_sn AND uc.user_id = #{userId} " +
            "GROUP BY charging_date " +
            "ORDER BY charging_date")
    /**
     * 该用户所拥有的所有设备每一天充电电量的总和,只统计最近7天
     */
    List<DayChargingEnergy> getTotalChargingEnergyByUserId(@Param("userId") Long userId);

    @Results({
            @Result(property = "date", column = "charging_date"),
            @Result(property = "time", column = "total_charging_time")
    })
    @Select("WITH RECURSIVE DateRange AS (" +
            "    SELECT CURDATE() - INTERVAL 6 DAY AS reporting_date " +
            "    UNION ALL " +
            "    SELECT reporting_date + INTERVAL 1 DAY " +
            "    FROM DateRange " +
            "    WHERE reporting_date < CURDATE() " +
            ") " +
            "SELECT DATE(dr.reporting_date) AS charging_date, COALESCE(SUM(dc.charging_time), 0) AS total_charging_time " +
            "FROM DateRange dr " +
            "LEFT JOIN hs_device_charger_data dc ON DATE(dc.report_time) = DATE(dr.reporting_date) " +
            "LEFT JOIN hs_device_user_charger uc ON dc.sn = uc.charger_sn AND uc.user_id = #{userId} " +
            "GROUP BY charging_date " +
            "ORDER BY charging_date")
    /**
     * 该用户所拥有的所有设备每一天充电时长的总和,只统计最近7天,单位分钟
     */
    List<DayChargingTime> getTotalChargingTimeByUserId(@Param("userId") Long userId);
}
